package com.hnkywl.sts.dao.cggl;

import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.entity.cggl.Cgd;
import com.hnkywl.sts.entity.cggl.Cgdmx;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.service.sys.YgService;
import com.hnkywl.sts.util.StringFormat;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-5-7
 * Time: 下午8:35
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CgdmxDao extends SimpleHibernateTemplate<Cgdmx, Long> {
    private YgDao ygDao;

    public List<Cgdmx> findCgdIdsByWzbm(String wzbm) {
        String hql = " from Cgdmx where gg.code like ? or gg.oldcode like ?";
        return find(hql, wzbm + "%", wzbm + "%");
    }

    public void changeCgdmxMxh(Long mxh, Cgdmx cgdmx) {
        String htSql = "update t_biz_cgdmx set mxh = ? where id = ?";
        this.executeSQL(htSql, mxh, cgdmx.getId());
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }


    //采购明细查询列表
    public Page<Cgdmx> findAllCgdmxList(String cgdh, Long gysId, Long ckId, String code, String name, String ggxh, Long sbgsId, Long sbbmId, String zt, Date fhrqStart, Date fhrqEnd, int pageNo, int pageSize) throws ParseException {
        Page<Cgdmx> page = new Page<Cgdmx>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("cgd", "cgd", CriteriaSpecification.INNER_JOIN)
                .createAlias("cgd.sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd.sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd.gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd.dzck", "ck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cgd.cgdh").as("cgdh"))
                                .add(Property.forName("gg.name").as("gg_name"))
                                .add(Property.forName("gg.code").as("gg_code"))
                                .add(Property.forName("gg.oldcode").as("gg_oldcode"))
                                .add(Projections.sqlProjection("gg5_.ggxh as gg_ggxh", new String[]{"gg_ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("gg.cz").as("gg_cz"))
                                .add(Property.forName("gg.dw").as("gg_dw"))
                                .add(Property.forName("gg.th").as("gg_th"))
                                .add(Property.forName("gys.id").as("gys_id"))
                                .add(Property.forName("gys.jc").as("gys_jc"))
                                .add(Property.forName("ck.mc").as("ck_mc"))
                                .add(Property.forName("ck.id").as("ck_id"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbgs.id").as("sbgs_id"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbbm.id").as("sbbm_id"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("rksl").as("rksl"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("hse").as("hse"))
                                .add(Property.forName("bhse").as("bhse"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("cgd.zt").as("zt"))
                                .add(Property.forName("cgd.yrk").as("yrk"))
                                .add(Property.forName("cgd.rksj").as("rksj"))
                                .add(Projections.sqlProjection("this_.yjs as zt_yjs", new String[]{"zt_yjs"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("createUser.xm").as("createUser_xm"))
                                .add(Property.forName("lastUpdated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgd.cgdh").like(cgdh, MatchMode.START));
        }

        if (gysId != null) {
            criteria.add(Property.forName("gys.id").eq(gysId));
        }

        if (ckId != null) {
            criteria.add(Restrictions.eq("ck.id", ckId));
        }

        if (StringUtils.isNotEmpty(code)) {
            criteria.add(Restrictions.or(Property.forName("gg.code").like(code, MatchMode.START), Property.forName("gg.oldcode").like(code, MatchMode.START)));
        }

        if (StringUtils.isNotEmpty(name)) {
            criteria.add(Property.forName("gg.name").like(name, MatchMode.START));
        }

        if (StringUtils.isNotEmpty(ggxh)) {
            criteria.add(Restrictions.or(
                    Property.forName("gg.ggxh").like(ggxh, MatchMode.ANYWHERE),
                    Property.forName("gg.cz").like(ggxh, MatchMode.ANYWHERE)));
        }

        if (sbgsId != null) {
            criteria.add(Restrictions.eq("cgd.sbgs.id", sbgsId));
        }

        if (sbbmId != null) {
            criteria.add(Restrictions.eq("cgd.sbbm.id", sbbmId));
        }

        if (zt != null && !zt.isEmpty() && "yjs".equals(zt)) {
            criteria.add(Restrictions.eq("yjs", true));
        } else if (zt != null && !zt.isEmpty() && "yrk".equals(zt)) {
            criteria.add(Restrictions.eq("cgd.yrk", true));
        } else {
            criteria.add(Restrictions.or(Restrictions.eq("yjs", true), Restrictions.eq("cgd.yrk", true)));
        }

        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("kh.dateCreated", sDaysAfterNowDate(fhrqStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("kh.dateCreated", start));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("kh.dateCreated", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("kh.dateCreated", end));
        }
        criteria.add(Restrictions.isNotNull("cgd"));

        createCgdmxDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    private void createCgdmxDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
            }
        } else {
            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }

            criteria.add(
                    Restrictions.or(se,
                            Restrictions.eq("cgd.sbgs.id", YgService.getCurrentYgZgGsId())
                    )
            );
        }
    }

    private String createSQLDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


//            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStr).replace("{1}", "kh.");
        } else {
            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ( {1}CREATEORGAN_ID =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    regionSql += "  {0}   {1}createuser_id =" + YgService.getCurrentYg().getId();
                    break;
            }

            if (YgService.getCurrentYgCxfw() != 0) {
                regionSql += " or {1}CGD_ID in (select cgd.id from t_biz_cgd cgd where cgd.deleted!=1 and cgd.sbgs_id =" + YgService.getCurrentYgZgGsId() + "))";
            }

            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        }

    }

    public List<Map> findSumCgdmx(String cgdh, Long gysId, Long ckId, String code, String name, String ggxh, Long sbgsId, Long sbbmId, String zt, Date fhrqStart, Date fhrqEnd) {
        String select = "select sum(t.sbsl) as \"sumsl\",sum(t.rksl) as \"sumrksl\",sum(t.sbsl * t.hsj) as \"sumhse\",sum(t.sbsl * t.bhsj) as \"sumbhse\" ";
        String from = " from t_biz_cgdmx t inner join t_biz_cgd c on t.cgd_id = c.id left join t_sys_gsjg g on c.gys_id = g.id left join t_biz_kh kh on t.kh_id = kh.id left join t_sys_ck ck on kh.ck_id = ck.id left join t_sys_gg gg on gg.id = t.gg_id left join t_sys_gsjg g1 on c.sbgs_id = g1.id left join t_sys_gsjg g2 on c.sbbm_id = g2.id where t.deleted != 1 ";
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(cgdh)) {
            where.append(" and ");
            where.append("c.cgdh like :cgdh");
        }
        if (gysId != null) {
            where.append(" and ");
            where.append("g.id = :gysId");
        }
        if (ckId != null) {
            where.append(" and ");
            where.append("ck.id = :ckId");
        }
        if (StringUtils.isNotEmpty(code)) {
            where.append(" and ");
            where.append(" (gg.code like :code or gg.oldcode like :oldcode) ");
        }
        if (StringUtils.isNotEmpty(name)) {
            where.append(" and ");
            where.append("gg.name like :name");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            where.append(" and ");
            where.append("(gg.ggxh like :ggxh or gg.cz like :cz)");
        }
        if (sbgsId != null) {
            where.append(" and ");
            where.append("g1.id = :sbgsId");
        }
        if (sbbmId != null) {
            where.append(" and ");
            where.append("g2.id = :sbbmId");
        }
        if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
            where.append(" and ");
            where.append("c.zt = :zt");
        } else if (zt != null && !zt.isEmpty() && "yrk".equals(zt)) {
            where.append(" and ");
            where.append("c.yrk = :zt");
        } else if (zt != null && !zt.isEmpty() && "yjs".equals(zt)) {
            where.append(" and ");
            where.append("t.yjs = :zt");
        } else {
            where.append(" and ");
            where.append("(c.yrk = :yrk or t.yjs=:yjs)");
        }

        if (fhrqStart != null) {
            where.append(" and ");
            where.append(" kh.date_created>= :fhrqStart ");
        } else {
            where.append(" and ");
            where.append(" kh.date_created>= :fhrqStart ");
        }
        if (fhrqEnd != null) {
            where.append(" and ");
            where.append(" kh.date_created<= :fhrqEnd ");
        } else {
            where.append(" and ");
            where.append(" kh.date_created<= :fhrqEnd ");
        }

        where.append(" and t.cgd_id is not null ");

        String dataRegion = createSQLDataRegion(null, "t");
        SQLQuery query = getSession().createSQLQuery(select + from + where + " and " + dataRegion);
        if (StringUtils.isNotEmpty(cgdh)) {
            query.setString("cgdh", cgdh.trim() + "%");
        }
        if (gysId != null) {
            query.setLong("gysId", gysId);
        }
        if (ckId != null) {
            query.setLong("ckId", ckId);
        }
        if (StringUtils.isNotEmpty(code)) {
            query.setString("code", code.trim() + "%");
            query.setString("oldcode", code.trim() + "%");
        }
        if (StringUtils.isNotEmpty(name)) {
            query.setString("name", name.trim() + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            query.setString("ggxh", "%" + ggxh.trim() + "%");
            query.setString("cz", "%" + ggxh.trim() + "%");
        }
        if (sbgsId != null) {
            query.setLong("sbgsId", sbgsId);
        }
        if (sbbmId != null) {
            query.setLong("sbbmId", sbbmId);
        }

        if (zt != null && !zt.isEmpty() && "yrk".equals(zt)) {
            query.setBoolean("zt", true);
        } else if (zt != null && !zt.isEmpty() && "yjs".equals(zt)) {
            query.setBoolean("zt", true);
        } else {
            query.setBoolean("yrk", true);
            query.setBoolean("yjs", true);
        }

        if (fhrqStart != null) {
            query.setParameter("fhrqStart", sDaysAfterNowDate(fhrqStart));
        } else {
            query.setParameter("fhrqStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            query.setParameter("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            query.setParameter("fhrqEnd", jDaysAfterNowDate(new Date()));
        }

        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public List<Map> finCgdmxs(Long[] ids) {
        String strIds = "";
        for (Long id : ids) {
            strIds += id.toString() + ",";
        }

        strIds = strIds.substring(0, strIds.length() - 1);
        String sql = "select gg.name name,gg.code code,gg.ggxh ggxh,t.bhsj wsdj,gg.dw dw from t_biz_cgdmx t " +
                "left join t_sys_gg gg  on gg.id = t.gg_id " +
                "where t.id in ({0})";
        SQLQuery query = getSession().createSQLQuery(StringFormat.format(sql, strIds));
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public List<Map> findCgdmxList(String cgdh, Long gysId, Long ckId, String code, String name, String ggxh, Long sbgsId, Long sbbmId, String zt, Date fhrqStart, Date fhrqEnd) {
        String select = " select t.cgd_id as \"cgdId\",c.cgdh as \"cgdh\", " +
                " gg.id as \"gg.id\",gg.name as \"gg.name\",gg.code as \"gg.code\",gg.oldcode as \"gg.oldcode\"," +
                " gg.ggxh as \"gg.ggxh\",gg.cz as \"gg.cz\",gg.dw as \"gg.dw\",gg.th as \"gg.th\", t.sbsl as \"sbsl\", t.rksl as \"rksl\", t.hsj as \"hsj\", t.bhsj as \"bhsj\"," +
                " (t.hsj * t.rksl) as \"hse\",  ck.id as \"ckId\",ck.mc as \"ckmc\",g.id as \"gysId\", g.jc as \"gysJc\",g1.id as \"sbgsId\", g1.jc as \"sbgsJc\", g2.id as \"sbbmId\",g2.qc as \"sbbmQc\" " +
                " from t_biz_cgdmx t inner join t_biz_cgd c on t.cgd_id = c.id left join t_sys_gsjg g  on c.gys_id = g.id " +
                " left join t_biz_kh kh on t.kh_id = kh.id left join t_sys_ck ck on kh.ck_id = ck.id " +
                " left join t_sys_gg gg  on gg.id = t.gg_id left join t_sys_gsjg g1  on c.sbgs_id = g1.id " +
                " left join t_sys_gsjg g2 on c.sbbm_id = g2.id where t.deleted!=1";
        String group = "  order by t.date_created desc";
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(cgdh)) {
            where.append(" and ");
            where.append("c.cgdh like :cgdh");
        }
        if (gysId != null) {
            where.append(" and ");
            where.append("g.id = :gysId");
        }
        if (ckId != null) {
            where.append(" and ");
            where.append("ck.id = :ckId");
        }
        if (StringUtils.isNotEmpty(code)) {
            where.append(" and ");
            where.append(" (gg.code like :code or gg.oldcode like :oldcode) ");
        }
        if (StringUtils.isNotEmpty(name)) {
            where.append(" and ");
            where.append("gg.name like :name");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            where.append(" and ");
            where.append("(gg.ggxh like :ggxh or gg.cz like :cz)");
        }
        if (sbgsId != null) {
            where.append(" and ");
            where.append("g1.id = :sbgsId");
        }
        if (sbbmId != null) {
            where.append(" and ");
            where.append("g2.id = :sbbmId");
        }

        if (zt != null && !zt.isEmpty() && "yrk".equals(zt)) {
            where.append(" and ");
            where.append("c.yrk = :zt");
        } else if (zt != null && !zt.isEmpty() && "yjs".equals(zt)) {
            where.append(" and ");
            where.append("t.yjs = :zt");
        } else {
            where.append(" and ");
            where.append("(c.yrk = :yrk or t.yjs=:yjs)");
        }

        if (fhrqStart != null) {
            where.append(" and ");
            where.append(" c.rksj>= :fhrqStart ");
        } else {
            where.append(" and ");
            where.append(" c.rksj>= :fhrqStart ");
        }
        if (fhrqEnd != null) {
            where.append(" and ");
            where.append(" c.rksj<= :fhrqEnd ");
        } else {
            where.append(" and ");
            where.append(" c.rksj<= :fhrqEnd ");
        }

        String dataRegion = createSQLDataRegion(null, "t");
        SQLQuery query = getSession().createSQLQuery(select + where + " and " + dataRegion + group);
        if (StringUtils.isNotEmpty(cgdh)) {
            query.setString("cgdh", "%" + cgdh.trim() + "%");
        }
        if (gysId != null) {
            query.setLong("gysId", gysId);
        }
        if (ckId != null) {
            query.setLong("ckId", ckId);
        }
        if (StringUtils.isNotEmpty(code)) {
            query.setString("code", code.trim() + "%");
            query.setString("oldcode", code.trim() + "%");
        }
        if (StringUtils.isNotEmpty(name)) {
            query.setString("name", name.trim() + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            query.setString("ggxh", "%" + ggxh.trim() + "%");
            query.setString("cz", "%" + ggxh.trim() + "%");
        }
        if (sbgsId != null) {
            query.setLong("sbgsId", sbgsId);
        }
        if (sbbmId != null) {
            query.setLong("sbbmId", sbbmId);
        }

        if (zt != null && !zt.isEmpty() && "yrk".equals(zt)) {
            query.setBoolean("zt", true);
        } else if (zt != null && !zt.isEmpty() && "yjs".equals(zt)) {
            query.setBoolean("zt", true);
        } else {
            query.setBoolean("yrk", true);
            query.setBoolean("yjs", true);
        }

        if (fhrqStart != null) {
            query.setParameter("fhrqStart", sDaysAfterNowDate(fhrqStart));
        } else {
            query.setParameter("fhrqStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            query.setParameter("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            query.setParameter("fhrqEnd", jDaysAfterNowDate(new Date()));
        }

        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }


    public Cgdmx findCgdmxByBindNo(String bindNo) {
        String hql = "from Cgdmx where bindNo = ? ";
        List<Cgdmx> list = this.find(hql, bindNo);
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }

}
